﻿Imports System.Data.SqlClient
Public Class FormStock

    Private Sub FormStock_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim cn As New SqlConnection()
        cn.ConnectionString = "server=WINCTRL-B8GV7ME;uid=sa;pwd=123;database=AppSaleDb;"
        cn.Open()

        Dim cmd As New SqlCommand()
        cmd.Connection = cn
        cmd.CommandText = "SELECT * FROM tbl_Products"
        ' WHERE CustomerName LIKE N'%" & txtSearch.Text & "%' Or Phone Like N'%" & txtSearch.Text & "%' or CustomerId Like N'%" & txtSearch.Text & "%'"
        Dim reader As SqlDataReader = cmd.ExecuteReader()

        While reader.Read()
            DataGridViewStock.Rows.Add(reader.GetValue(0), reader.GetValue(1), reader.GetValue(2), reader.GetValue(3), reader.GetValue(4), reader.GetValue(5), reader.GetValue(6))
            ' DataGridViewCustomer.Rows.Add(reader.GetValue(1), reader.GetValue(2), reader.GetValue(3))
        End While
        reader.Close()
        '==============Total=====================
        If DataGridViewStock.RowCount > 1 Then
            Dim iQty As Decimal = 0
            Dim iPriceIn As Decimal = 0
            Dim iTotalIn As Decimal = 0
            Dim iPriceOut As Integer = 0
            For index As Integer = 0 To DataGridViewStock.RowCount - 1
                iQty += Convert.ToInt32(DataGridViewStock.Rows(index).Cells(2).Value)
                iPriceIn += Convert.ToInt32(DataGridViewStock.Rows(index).Cells(3).Value)
                iTotalIn += Convert.ToInt32(DataGridViewStock.Rows(index).Cells(4).Value)
                iPriceOut += Convert.ToInt32(DataGridViewStock.Rows(index).Cells(5).Value)
            Next
            LabelQty.Text = iQty '("ចំនូន")
            LabelPriceIn.Text = iPriceIn
            LabelTotaIn.Text = iTotalIn
            LabelPriceOut.Text = iPriceOut
        End If

    End Sub

    Private Sub txtSearch_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSearch.TextChanged
        DataGridViewStock.Rows.Clear()
        Dim cn As New SqlConnection()
        cn.ConnectionString = "server=WINCTRL-B8GV7ME;uid=sa;pwd=123;database=AppSaleDb;"
        cn.Open()

        Dim cmd As New SqlCommand()
        cmd.Connection = cn
        cmd.CommandText = "SELECT * FROM tbl_Products WHERE Code LIKE N'%" & txtSearch.Text & "%' Or Name Like N'%" & txtSearch.Text & "%' or Qty Like N'%" & txtSearch.Text & "%'"
        Dim reader As SqlDataReader = cmd.ExecuteReader()

        While reader.Read()
            DataGridViewStock.Rows.Add(reader.GetValue(0), reader.GetValue(1), reader.GetValue(2), reader.GetValue(3), reader.GetValue(4), reader.GetValue(5), reader.GetValue(6))
        End While
        reader.Close()
    End Sub

    Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click
        FormAddEditorStock.ShowDialog()
    End Sub

    Private Sub AddEdite_Click(sender As System.Object, e As System.EventArgs) Handles AddEdite.Click
        If Me.DataGridViewStock.SelectedRows.Count = 0 Then
            Exit Sub
        End If
        Dim row As DataGridViewRow = Me.DataGridViewStock.SelectedRows(0)
        Dim frm As New FormAddEditorStock()
        frm.txtCode.ReadOnly = True
        frm.txtCode.Text = row.Cells("Code").Value
        frm.txtNamePro.Text = row.Cells("NamePro").Value
        frm.txtPriceIn.Text = row.Cells("PriceIn").Value
        frm.txtQty.Text = row.Cells("Qty").Value
        frm.txtTotalInPrice.Text = row.Cells("Total").Value
        frm.txtPriceOut.Text = row.Cells("PriceOut").Value
        frm.dtpStock.Text = row.Cells("DateIn").Value
        If frm.ShowDialog() = Windows.Forms.DialogResult.OK Then
            DataGridViewStock.Rows.Clear()
            Dim cn As New SqlConnection()
            cn.ConnectionString = "server=WINCTRL-B8GV7ME;uid=sa;pwd=123;database=AppSaleDb;"
            cn.Open()

            Dim cmd As New SqlCommand()
            cmd.Connection = cn
            cmd.CommandText = "SELECT * FROM tbl_Products WHERE Code LIKE N'%" & txtSearch.Text & "%' Or Name Like N'%" & txtSearch.Text & "%' or Qty Like N'%" & txtSearch.Text & "%'"
            Dim reader As SqlDataReader = cmd.ExecuteReader()

            While reader.Read()
                DataGridViewStock.Rows.Add(reader.GetValue(0), reader.GetValue(1), reader.GetValue(2), reader.GetValue(3), reader.GetValue(4), reader.GetValue(5), reader.GetValue(6))
            End While
            reader.Close()
        End If
    End Sub

    Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
        If Me.DataGridViewStock.SelectedRows.Count = 0 Then
            Exit Sub
        End If
        If MessageBox.Show("តើអ្នកចង់លុបទិន្នន័យដែលឬទេ?", "លុបទិន្នន័យ", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If
        Dim row As DataGridViewRow = Me.DataGridViewStock.SelectedRows(0)
        Dim Code = row.Cells("Code").Value

        Dim cn As New SqlConnection()
        cn.ConnectionString = "server=WINCTRL-B8GV7ME;uid=sa;pwd=123;database=SaleProDb;"
        cn.Open()

        Dim cmd As New SqlCommand()
        cmd.Connection = cn
        cmd.CommandText = "DELETE FROM tbl_Products WHERE Code =" & Code
        cmd.ExecuteNonQuery()

        '============Refrace Data =================
        DataGridViewStock.Rows.Clear()
        'Dim cn As New SqlConnection()
        'cn.ConnectionString = "server=.;uid=sa;pwd=123;database=AppSaleDb;"
        'cn.Open()

        'Dim cmd As New SqlCommand()
        cmd.Connection = cn
        cmd.CommandText = "SELECT * FROM tbl_Products WHERE Code LIKE N'%" & txtSearch.Text & "%' Or Name Like N'%" & txtSearch.Text & "%' or Qty Like N'%" & txtSearch.Text & "%'"
        Dim reader As SqlDataReader = cmd.ExecuteReader()

        While reader.Read()
            DataGridViewStock.Rows.Add(reader.GetValue(0), reader.GetValue(1), reader.GetValue(2), reader.GetValue(3), reader.GetValue(4), reader.GetValue(5), reader.GetValue(6))
        End While
        reader.Close()
    End Sub

   
End Class